const connection = require('../../database/connection')

module.exports = {
    async getScreen(data) {
        const [screens] = await connection.execute('SELECT p.visit_plan, p.sign_plan FROM `plans` p, `departments` d, `centers` c WHERE p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND d.`id` = p.`department_id` AND c.`id` = p.`center_id` AND p.`deleted_at` IS NULL AND d.`deleted_at` IS NULL ORDER BY p.`id`;')
        return screens
    },
    async getScreenLC(data) {
        const [screenLCs] = await connection.execute('SELECT s.`name` salesman_name, count( * ) amount FROM `records` r, `salesmans` s WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" ' + data.select + ' AND ' + data.sql + ' r.`salesman_id` = s.`id` AND r.`deleted_at` IS NULL AND s.`deleted_at` IS NULL GROUP BY r.`salesman_id` HAVING amount >= 1 ORDER BY amount DESC LIMIT 10;')
        return screenLCs
    },
    async getScreenRC(data) {
        const [screenRCs] = await connection.execute('SELECT wm.`name` warrant_man, count( * ) amount FROM `records` r, `warrant_mans` wm WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '"  AND ' + data.sql + ' r.`warrant_man_id` = wm.`id` AND r.`deleted_at` IS NULL AND wm.`deleted_at` IS NULL GROUP BY r.`warrant_man_id` HAVING amount >= 1 ORDER BY amount DESC LIMIT 10;')
        return screenRCs
    },
    async getWarrantMansNames() {
        const [warrantMansNames] = await connection.execute('SELECT wm.`name` FROM `warrant_mans` wm WHERE wm.`deleted_at` IS NULL ORDER BY wm.`id`;')
        return warrantMansNames
    },
    async getScreenSphere(data) {
        const [screenSpheres] = await connection.execute('SELECT s.`name` salesman_name, count( * ) amount FROM `records` r, `salesmans` s WHERE ' + data.time + ' >= "' + data.from + '" AND ' + data.time + ' <= "' + data.to + '" AND ' + data.sql + ' r.`salesman_id` = s.`id` AND r.`deleted_at` IS NULL AND s.`deleted_at` IS NULL GROUP BY r.`salesman_id` HAVING amount >= 1 ORDER BY amount DESC;')
        return screenSpheres
    },
    async getScreenRecordLT(data) {
        const [screenRecords] = await connection.execute('SELECT count( * ) amount FROM `records` r, `centers` c, `departments` d WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '" AND ' + data.sql + ' c.`id` = r.`center_id` AND d.`id` = r.`department_id` AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY r.`department_id` HAVING amount >= 1;')
        return screenRecords
    },
    async getScreenRecordRB(data) {
        const [screenRecords] = await connection.execute('SELECT r.`time`, count( * ) amount FROM `records` r WHERE r.`time` >= "' + data.from + '" AND r.`time` <= "' + data.to + '" ' + data.sql + ' AND r.`deleted_at` IS NULL GROUP BY r.`time` HAVING amount >= 1;')
        return screenRecords
    },
    async getScreenRecordPicksRB(data) {
        const [screenRecords] = await connection.execute('SELECT r.`pick_car_time` time, count( * ) amount FROM `records` r WHERE r.`pick_car_time` >= "' + data.from + '" AND r.`pick_car_time` <= "' + data.to + '" ' + data.sql + ' AND r.`deleted_at` IS NULL GROUP BY r.`pick_car_time` HAVING amount >= 1;')
        return screenRecords
    },
    async getScreenRecordRT(data) {
        const [visitsRate] = await connection.execute('SELECT ANY_VALUE (c.`name`) `name`, count(*) amount FROM `records` r, `centers` c WHERE r.`' + data.time + '` >= "' + data.from + '" AND r.`' + data.time + '` <= "' + data.to + '" AND ' + data.sql + ' c.`id` = r.`center_id` AND r.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY r.`center_id` having amount>=1;')
        return visitsRate
    },
    async getStats_plansOfCenter(data) {
        const [plans] = await connection.execute('SELECT ANY_VALUE ( c.`name` ) `name`, SUM( `visit_plan` ) AS `visit_plan`, SUM( `sign_plan` ) AS `sign_plan`, count(*) amount FROM `plans` p, `centers` c WHERE p.`time` >= "' + data.from + '" AND p.`time` <= "' + data.to + '" AND c.`id` = p.`center_id` AND c.`name` = "' + data.center + '" AND p.`deleted_at` IS NULL AND c.`deleted_at` IS NULL GROUP BY p.`time` having amount>=1 ORDER BY amount;')
        return plans
    },
    async getScreenDepts() {
        const [screenDepts] = await connection.execute('SELECT c.`name` center_name, d.`name` dept_name FROM `centers` c, `departments` d WHERE d.`center_id` = c.`id` AND c.`deleted_at` IS NULL AND d.`deleted_at` IS NULL;')
        return screenDepts
    },
};


